DROP DATABASE IF EXISTS school;
CREATE DATABASE `school` DEFAULT CHARACTER SET greek COLLATE greek_general_ci;
Use school;

CREATE TABLE `school`.`student` (
`name` VARCHAR( 25 ) NOT NULL DEFAULT 'unknown',
`lastname` VARCHAR( 25 ) NOT NULL DEFAULT 'unknown',
`am` INT( 5 ) NOT NULL AUTO_INCREMENT PRIMARY KEY
) ENGINE = InnoDB CHARACTER SET greek COLLATE greek_general_ci;


CREATE TABLE `school`.`professor` (
`pr_name` VARCHAR( 25 ) NOT NULL DEFAULT 'unknown',
`pr_lastname` VARCHAR( 25 ) NOT NULL DEFAULT 'unknown',
`email` VARCHAR( 255 ) NOT NULL ,
PRIMARY KEY ( `email` )
) ENGINE = InnoDB CHARACTER SET greek COLLATE greek_general_ci;


CREATE TABLE `school`.`course` (
`title` VARCHAR( 255 ) NOT NULL DEFAULT 'unknown',
`material` TEXT NULL ,
`course_id` INT( 4 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`supervisor` VARCHAR( 255 ) NOT NULL ,
UNIQUE (`title`),
CONSTRAINT SUPERVISED 
FOREIGN KEY (supervisor) REFERENCES professor(email)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB CHARACTER SET greek COLLATE greek_general_ci;

CREATE TABLE `school`.`books` (
`title` VARCHAR( 128 ) NOT NULL DEFAULT 'Title',
`course_book` INT( 4 ) NOT NULL ,
PRIMARY KEY ( `title` , `course_book` ),
CONSTRAINT CRSBOOK
FOREIGN KEY (course_book) REFERENCES course(course_id)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB CHARACTER SET greek COLLATE greek_general_ci;


CREATE TABLE `school`.`lecture` (
`subject` VARCHAR( 128 ) NULL ,
`num_lecture` INT( 2 ) NOT NULL ,
`course_lecture` INT( 4 ) NOT NULL ,
PRIMARY KEY ( `num_lecture` , `course_lecture` ),
CONSTRAINT CRSLECTURE
FOREIGN KEY (course_lecture) REFERENCES course(course_id)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB CHARACTER SET greek COLLATE greek_general_ci;

CREATE TABLE `school`.`registration` (
`reg_date` DATE NOT NULL ,
`reg_student` INT( 5 ) NOT NULL ,
`reg_course` INT( 4 ) NOT NULL ,
PRIMARY KEY ( `reg_student` , `reg_course` ),
CONSTRAINT CRSREGISTRATION
FOREIGN KEY (reg_course) REFERENCES course(course_id)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT STDNTREGISTRATION
FOREIGN KEY (reg_student) REFERENCES student(AM)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB CHARACTER SET greek COLLATE greek_general_ci;

INSERT INTO `school`.`student` (`name` ,`lastname` ,`am`) VALUES ('Βιβή', 'Τζέκου', '2191');
INSERT INTO `school`.`student` (`lastname` ,`am`) VALUES ( 'Ντούρου', '2192');
INSERT INTO `school`.`student` (`name` ,`lastname` ,`am`) VALUES ('Αθανασία', 'Κουμπούρη', '2193');
INSERT INTO `school`.`student` (`name` ,`am`) VALUES ('Βάσω', '1845');

INSERT INTO `school`.`professor` (`pr_name` , `pr_lastname`, `email`) VALUES ('Μαρία', 'Παπαδοπούλου', 'pap@ceid.upatras.gr');
INSERT INTO `school`.`professor` (`pr_lastname` ,`email`) VALUES ('Δημητρίου', 'dim@upatras.gr');
INSERT INTO `school`.`professor` (`pr_name` ,`email`) VALUES ('Αλεξάνδρα', 'alex@upatras.gr');

INSERT INTO `school`.`course` (`title` ,`material` ,`course_id` ,`supervisor`) VALUES ('Βάσεις Δεδομένων', 'Εισαγωγή σε σχεσιακές βάσεις δεδομένων', '2', 'pap@ceid.upatras.gr');
INSERT INTO `school`.`course` (`title` ,`material` ,`course_id` ,`supervisor`) VALUES ('Βάσεις Δεδομένων II', 'Προχωρημένα θέματα βάσεων δεδομένων', '3', 'alex@upatras.gr');

INSERT INTO `school`.`books` (`title` ,`course_book`) VALUES ('Databases 1', '2');
INSERT INTO `school`.`books` (`title` ,`course_book`) VALUES ('Databases 1 2nd volume', '2');
INSERT INTO `school`.`books` (`title` ,`course_book`) VALUES ('Databases 2', '3');

INSERT INTO `school`.`lecture` (`subject` ,`num_lecture` ,`course_lecture`) VALUES ('Εισαγωγή σε βάσεις', '1', '2');
INSERT INTO `school`.`lecture` (`subject` ,`num_lecture` ,`course_lecture`) VALUES ('Ανάλυση απαιτήσεων', '2', '2');
INSERT INTO `school`.`lecture` (`subject` ,`num_lecture` ,`course_lecture`) VALUES ('ER-Σχεσιακό', '3', '2');
INSERT INTO `school`.`lecture` (`subject` ,`num_lecture` ,`course_lecture`) VALUES ('Κανονικοποίηση Βάσεων', '1', '3');
INSERT INTO `school`.`lecture` (`subject` ,`num_lecture` ,`course_lecture`) VALUES ('Βελτιστοποίηση Βάσεων', '2', '3');

INSERT INTO `school`.`registration` (`reg_date` ,`reg_student` ,`reg_course`) VALUES ('2010-08-09', '1845', '2');
INSERT INTO `school`.`registration` (`reg_date` ,`reg_student` ,`reg_course`) VALUES ('2011-02-11', '2191', '2');
INSERT INTO `school`.`registration` (`reg_date` ,`reg_student` ,`reg_course`) VALUES ('2012-06-03', '2192', '3');
INSERT INTO `school`.`registration` (`reg_date` ,`reg_student` ,`reg_course`) VALUES ('2010-01-12', '2193', '3');
INSERT INTO `school`.`registration` (`reg_date` ,`reg_student` ,`reg_course`) VALUES ('2011-04-19', '2191', '3');


